//
//  SQLManager.swift
//  iosCourse
//
//  Created by 武怡凡 on 2025/5/2.
//


import UIKit

let DBFILE_NAME = "User.sqlite"

public class SQLManager : NSObject {
   static let instance = SQLManager();
   var db : OpaquePointer? = nil
   class func shareInstance() -> SQLManager {
       return instance
   }
   
   func getFilePath() -> String {
       let documentPath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last
       let DBPath = (documentPath! as NSString).appendingPathComponent(DBFILE_NAME)
       print("数据库的地址是：\(DBPath)")
       return DBPath
   }
   
   func createDataBaseTableIfNeeded() {
       let cDBPath = getFilePath().cString(using: String.Encoding.utf8)
       
       if sqlite3_open(cDBPath, &db) != SQLITE_OK {
           print("数据库打开失败！")
       } else {
           print("数据库打开成功！")
           let createStudentTableSQL = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,email TEXT UNIQUE,password TEXT);"
           if execSQL(SQL: createStudentTableSQL) == false {
               print("执行创建表的SQL语句出错!")
           } else {
               print("创建表的SQL语句执行成功！")
           }
       }
   }
   
   func queryDataBase(querySQL : String) -> [[String : AnyObject]]? {
       var statement : OpaquePointer? = nil
       
       if querySQL.lengthOfBytes(using: String.Encoding.utf8) > 0 {
           let cQuerySQL = (querySQL.cString(using: String.Encoding.utf8))!
           if sqlite3_prepare_v2(db, cQuerySQL, -1, &statement, nil) == SQLITE_OK {
               var queryDataArr = [[String: AnyObject]]()
               while sqlite3_step(statement) == SQLITE_ROW {
                   let columnCount = sqlite3_column_count(statement)
                   var temp = [String : AnyObject]()
                   for i in 0..<columnCount {
                       let cKey = sqlite3_column_name(statement, i)
                       let key : String = String(validatingUTF8: cKey!)!
                       let cValue = sqlite3_column_text(statement, i)
                       let value = String(cString: cValue!)
                       temp[key] = value as AnyObject
                   }
                   queryDataArr.append(temp)
               }
               return queryDataArr
           }
       }
       return nil
   }
   
   func execSQL(SQL : String) -> Bool {
       let cSQL = SQL.cString(using: String.Encoding.utf8)
       let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil
       if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK {
           return true
       } else {
           print("执行SQL语句时出错，错误信息为：\(errmsg)")
           return false
       }
   }
}
